/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package cn.com.jsepc.rep.statistics;

import cn.com.jsepc.rep.comm.ConstUtil;
import cn.com.jsepc.rep.vo.StatisticsVO;
import com.jarp.db.DAO;

import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.List;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.ServletException;
import net.sf.json.JSONArray;

/**
 *
 * @author 赵远 获取统计数据
 */
public class GetStatistics {


    public String getStatisticsGrid() {
        String res = "";
        DecimalFormat df=new DecimalFormat("######0.00");  
        DAO dao = new DAO();
        JSONArray json = new JSONArray();
        StringBuffer str = new StringBuffer();
        str.append("select d.cataid,d.CATANAME");
        str.append("\n,value(G,0) G");                                                                
        str.append("\n,value(J,0) J");                                                        
        str.append("\n,value(CG,0) CG");                                                      
        str.append("\n,value(CJ,0) CJ");                                                      
        str.append("\n from");                                                                
        str.append("\n(select cataid,CATANAME from tb_catalog d where d.PARENTCATAID=108) d");
        str.append("\nleft join (");                                                          
        str.append("\nselect cataid,count(distinct repid) G from (");                         
        str.append("\nselect a.repid,a.cataid");                                              
        str.append("\n from tb_report a");                                                    
        str.append("\nwhere");                                                                
        str.append("\na.SOURCETYPE=2 and a.ISVALID=1");                                       
        str.append("\nand a.repcode like 'G%'");                                              
        str.append("\n) z");                                                                  
        str.append("\ngroup by  cataid");                                                     
        str.append("\n) canshuall on canshuall.CATAID=d.CATAID");                             
        str.append("\nleft join (");                                                          
        str.append("\nselect cataid,count(distinct repid) J  from (");                        
        str.append("\nselect a.repid,a.cataid");                                              
        str.append("\n from tb_report a");                                                    
        str.append("\nwhere");                                                                
        str.append("\na.SOURCETYPE=2 and a.ISVALID=1");                                       
        str.append("\nand a.repcode like 'J%'");                                              
        str.append("\n) z");                                                                  
        str.append("\ngroup by  cataid");                                                     
        str.append("\n) canshuhavdem on canshuhavdem.CATAID=d.CATAID");                       
        str.append("\nleft join (");                                                         
        str.append("\nselect cataid,count(distinct repid) CG  from (");                      
        str.append("\nselect a.repid,a.cataid");                                              
        str.append("\n from tb_report a ");                                                   
        str.append("\nwhere");                                                                
        str.append("\na.SOURCETYPE=2 and a.ISVALID=1");                                      
        str.append("\nand a.repcode like 'CG%'");                                             
        str.append("\n) z");                                                                  
        str.append("\ngroup by  cataid");                                                     
        str.append("\n) canshunothavdem on canshunothavdem.CATAID=d.CATAID");                 
        str.append("\nleft join (");                                                          
        str.append("\nselect cataid,count(distinct repid) CJ  from (");                       
        str.append("\nselect a.repid,a.cataid");                                              
        str.append("\n from tb_report a");                                                    
        str.append("\nwhere");                                                                
        str.append("\na.SOURCETYPE=2 and a.ISVALID=1");                                       
        str.append("\nand a.repcode like 'CJ%'");                                             
        str.append("\n) z");                                                                  
        str.append("\ngroup by  cataid");                                                     
        str.append("\n) CJ on CJ.CATAID=d.CATAID");                                           
        str.append("\norder by d.CATAID");  
        
        try {
            List<StatisticsVO> lstResults = new ArrayList<StatisticsVO>();
//            System.out.println("str:"+str.toString());
            Vector v = dao.getDataSet(ConstUtil.DBNAME, str.toString());
//            System.out.println("Vector:"+v);
            StatisticsVO statisTot=new StatisticsVO();
            //将合计加入json
            statisTot.setCatalog("合计");
            double rep_all=0;
        	double rep_fill_all=0;
        	double J_rep_all=0;
        	double J_fill_all=0;
        	double G_rep_all=0;
        	double G_fill_all=0;
        	double CJ_rep_all=0;
        	double CJ_fill_all=0;
        	double CG_rep_all=0;
        	double CG_fill_all=0;
        	double para_rep_all=0;
        	double para_fill_all=0;
            if(v!=null){
            for(int i=0;i<v.size();i++){
            	StatisticsVO statis=new StatisticsVO();
                Hashtable hash=(Hashtable)v.get(i);
                statis.setCatalog((String)hash.get("CATANAME"));
                String J_rep=this.convertStr((String)hash.get("J"));
                String G_rep=this.convertStr((String)hash.get("G"));
                String J_fill=this.convertStr(this.getRep((String)hash.get("CATAID"), "J"));
                String G_fill=this.convertStr(this.getRep((String)hash.get("CATAID"), "G"));
                String Cj_rep=this.convertStr((String)hash.get("CJ"));
                String Cg_rep=this.convertStr((String)hash.get("CG"));
                String Cj_fill=this.convertStr(this.getRep((String)hash.get("CATAID"), "CJ"));
                String Cg_fill=this.convertStr(this.getRep((String)hash.get("CATAID"), "CG"));
                statis.setJ_rep(J_rep);
                statis.setJ_fill(J_fill);
                statis.setG_rep(G_rep);
                statis.setG_fill(G_fill);
                statis.setCj_rep(Cj_rep);
                statis.setCj_fill(Cj_fill);
                statis.setCg_rep(Cg_rep);
                statis.setCg_fill(Cg_fill);
                statis.setRep_all(String.valueOf(Integer.parseInt(statis.getJ_rep())+Integer.parseInt(statis.getG_rep())));   //指标总数（不包含参数）
                if(statis.getRep_all().equals("0")){
                	statis.setRep_fill("0");
                }
                else{
                statis.setRep_fill(String.valueOf(df.format(100*(Double.valueOf(statis.getJ_fill())+Double.valueOf(statis.getG_fill()))/
                        (Double.valueOf(statis.getJ_rep())+Double.valueOf(statis.getG_rep())))));
                }
                statis.setPara_all(String.valueOf(Integer.parseInt(statis.getCj_rep())+Integer.parseInt(statis.getCg_rep())));
                if(statis.getPara_all().equals("0")){
                	statis.setPara_fill("0");
                }
                else{
                statis.setPara_fill(String.valueOf(df.format(100*((Double.valueOf(statis.getCj_fill())+Double.valueOf(statis.getCg_fill()))/
                        (Double.valueOf(statis.getCj_rep())+Double.valueOf(statis.getCg_rep()))))));
                }
                //累加，用于计算合计
                rep_all+=Double.valueOf(statis.getRep_all());
                J_rep_all+=Double.valueOf(statis.getJ_rep());
                para_rep_all+=Double.valueOf(statis.getPara_all());
                J_fill_all+=Double.valueOf(statis.getJ_fill());
                G_rep_all+=Double.valueOf(statis.getG_rep());
                G_fill_all+=Double.valueOf(statis.getG_fill());
                CJ_rep_all+=Double.valueOf(statis.getCj_rep());
                CJ_fill_all+=Double.valueOf(statis.getCj_fill());
                CG_rep_all+=Double.valueOf(statis.getCg_rep());
                CG_fill_all+=Double.valueOf(statis.getCg_fill());
                lstResults.add(statis);
            }
           }
            statisTot.setRep_all(String.valueOf((int)rep_all));
            statisTot.setJ_rep(String.valueOf((int)J_rep_all));
            statisTot.setPara_all(String.valueOf((int)para_rep_all));
            statisTot.setJ_fill(String.valueOf((int)J_fill_all));
            statisTot.setG_rep(String.valueOf((int)G_rep_all));
            statisTot.setG_fill(String.valueOf((int)G_fill_all));
            statisTot.setCj_rep(String.valueOf((int)CJ_rep_all));
            statisTot.setCj_fill(String.valueOf((int)CJ_fill_all));
            statisTot.setCg_rep(String.valueOf((int)CG_rep_all));
            statisTot.setCg_fill(String.valueOf((int)CG_fill_all));
            if(J_rep_all+G_rep_all==0){
            	statisTot.setRep_fill("0");
            }
            else{
            	statisTot.setRep_fill(String.valueOf(df.format(100*((J_fill_all+G_fill_all)/(J_rep_all+G_rep_all)))));	
            }
            if(CJ_rep_all+CG_rep_all==0){
            	statisTot.setPara_fill("0");
            }
            else{
            	statisTot.setPara_fill(String.valueOf(df.format(100*((CJ_fill_all+CG_fill_all)/(CJ_rep_all+CG_rep_all)))));
            }
            lstResults.add(statisTot);
            
            json = JSONArray.fromObject(lstResults);
            res = json.toString();
        } catch (ServletException ex) {
            Logger.getLogger(GetStatistics.class.getName()).log(Level.SEVERE, null, ex);
        }
        return res;
    }

    private String getRep(String cataid, String type) {
        String res = "0";
        DAO dao = new DAO();
        StringBuffer str = new StringBuffer();
        str.append("select a.REFFACT FROM tb_report a ");
        str.append("\n where a.CATAID=").append(cataid).append("              ");
        str.append("\n and a.SOURCETYPE=2              ");
        str.append("\n and a.ISVALID=1                 ");
        str.append("\n and a.repcode like '").append(type).append("%'         ");
        str.append("\n and a.REFFACT is not null and a.REFFACT <> ''     ");
        StringBuffer sql=new StringBuffer();
        sql.append("select count(1) cc from (");
        sql.append("\nselect * from (");
        try {
            Vector v = dao.getDataSet(ConstUtil.DBNAME, str.toString());
            if(v!=null){
            for(int i=0;i<v.size();i++){
                Hashtable hash=(Hashtable)v.get(i);
                if(i==v.size()-1){
                    sql.append("\n select '").append((String)hash.get("REFFACT")).append("',count(1) cc from ").append((String)hash.get("REFFACT")).append(" where repval is not null ");
                }
                else{
                    sql.append("\n select '").append((String)hash.get("REFFACT")).append("',count(1) cc from ").append((String)hash.get("REFFACT")).append(" where repval is not null union ");
                }
            }
            sql.append("\n ) z where cc > 0 ");
            sql.append("\n ) zz ");
            res=dao.getDataValue(ConstUtil.DBNAME, sql.toString());
            }
        } catch (ServletException ex) {
            Logger.getLogger(GetStatistics.class.getName()).log(Level.SEVERE, null, ex);
        }
        return res;
    }
    
    private String convertStr(String str){
    	try {
			if(str.equals("")||str==null){
				str="0";
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return str;
    }
}
